{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 8.3 Reshaping and Pivoting（整形和旋转）\n",
    "\n",
    "> 8.1中的操作名\n",
    "- join：连接\n",
    "- combine：合并\n",
    "- reshape：整形\n",
    "\n",
    "> 8.2中的操作名\n",
    "> - merge：归并\n",
    "- concatenate：串联\n",
    "\n",
    "> 8.3中的操作名\n",
    "- pivot：旋转\n",
    "- stack：堆叠\n",
    "\n",
    "> 我在整个第八章对这些翻译做了更新，其他章节可能没有统一，如果有发现到不统一的翻译，可以在issue里提出，也可以直接pull request\n",
    "\n",
    "\n",
    "有很多用于整理表格型数据的基本操作，指的就是reshape和pivot。\n",
    "\n",
    "# 1 Reshaping with Hierarchical Indexing（对多层级索引进行整形）\n",
    "\n",
    "多层级索引提供一套统一的方法来整理DataFrame中数据。主要有两个操作：\n",
    "\n",
    "stack\n",
    "- 这个操作会把列旋转为行\n",
    "\n",
    "unstack\n",
    "- 这个会把行变为列\n",
    "\n",
    "下面我们会给出一些例子。这里有一个DataFrame，我们用字符串数组来作为行和列的索引："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>number</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number    one  two  three\n",
       "state                    \n",
       "Ohio        0    1      2\n",
       "Colorado    3    4      5"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.arange(6).reshape((2, 3)),\n",
    "                    index=pd.Index(['Ohio', 'Colorado'], name='state'), \n",
    "                    columns=pd.Index(['one', 'two', 'three'], \n",
    "                    name='number'))\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用stack方法会把列数据变为行数据，产生一个Series："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state     number\n",
       "Ohio      one       0\n",
       "          two       1\n",
       "          three     2\n",
       "Colorado  one       3\n",
       "          two       4\n",
       "          three     5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = data.stack()\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对于一个有多层级索引的Series，可以用unstack把它变回DataFrame："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>number</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "number    one  two  three\n",
       "state                    \n",
       "Ohio        0    1      2\n",
       "Colorado    3    4      5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "默认会把最内层的层级unstack（取消堆叠），stack默认也是这样。我们可以传入一个表示层级的数字或名字，来指定取消堆叠某个层级："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>state</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state   Ohio  Colorado\n",
       "number                \n",
       "one        0         3\n",
       "two        1         4\n",
       "three      2         5"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.unstack(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>state</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state   Ohio  Colorado\n",
       "number                \n",
       "one        0         3\n",
       "two        1         4\n",
       "three      2         5"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.unstack('state')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果某个层级里的值不能在subgroup(子组)里找到的话，unstack可能会引入缺失值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "s1 = pd.Series([0, 1, 2, 3], index=['a', 'b', 'c', 'd'])\n",
    "\n",
    "s2 = pd.Series([4, 5, 6], index=['c', 'd', 'e'])\n",
    "\n",
    "data2 = pd.concat([s1, s2], keys=['one', 'two'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "one  a    0\n",
       "     b    1\n",
       "     c    2\n",
       "     d    3\n",
       "two  c    4\n",
       "     d    5\n",
       "     e    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a    b    c    d    e\n",
       "one  0.0  1.0  2.0  3.0  NaN\n",
       "two  NaN  NaN  4.0  5.0  6.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "stack默认会把缺失值过滤掉，所以这两种操作是可逆的："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a    b    c    d    e\n",
       "one  0.0  1.0  2.0  3.0  NaN\n",
       "two  NaN  NaN  4.0  5.0  6.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "one  a    0.0\n",
       "     b    1.0\n",
       "     c    2.0\n",
       "     d    3.0\n",
       "two  c    4.0\n",
       "     d    5.0\n",
       "     e    6.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack().stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "one  a    0.0\n",
       "     b    1.0\n",
       "     c    2.0\n",
       "     d    3.0\n",
       "     e    NaN\n",
       "two  a    NaN\n",
       "     b    NaN\n",
       "     c    4.0\n",
       "     d    5.0\n",
       "     e    6.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2.unstack().stack(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果对一个DataFrame使用unstack，被取消堆叠（unstack）的层级会变为结果中最低的层级："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>side</th>\n",
       "      <th>left</th>\n",
       "      <th>right</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Ohio</th>\n",
       "      <th>one</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Colorado</th>\n",
       "      <th>one</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "side             left  right\n",
       "state    number             \n",
       "Ohio     one        0      5\n",
       "         two        1      6\n",
       "         three      2      7\n",
       "Colorado one        3      8\n",
       "         two        4      9\n",
       "         three      5     10"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'left': result, 'right': result + 5}, \n",
    "                  columns=pd.Index(['left', 'right'], name='side'))\n",
    "df # 行的话，有state和number两个层级，number是内层级。而列的话有side这一个层级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>side</th>\n",
       "      <th colspan=\"2\" halign=\"left\">left</th>\n",
       "      <th colspan=\"2\" halign=\"left\">right</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "side   left          right         \n",
       "state  Ohio Colorado  Ohio Colorado\n",
       "number                             \n",
       "one       0        3     5        8\n",
       "two       1        4     6        9\n",
       "three     2        5     7       10"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.unstack('state')  # state被unstack后，变为比side更低的层级"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "调用stack的时候，可以指明想要stack（堆叠）哪一个轴："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number</th>\n",
       "      <th>side</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">one</th>\n",
       "      <th>left</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">two</th>\n",
       "      <th>left</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">three</th>\n",
       "      <th>left</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state         Ohio  Colorado\n",
       "number side                 \n",
       "one    left      0         3\n",
       "       right     5         8\n",
       "two    left      1         4\n",
       "       right     6         9\n",
       "three  left      2         5\n",
       "       right     7        10"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.unstack('state').stack('side')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2 Pivoting “Long” to “Wide” Format（把“长”格式旋转为“宽”格式）\n",
    "\n",
    "一种用来把多重时间序列数据存储在数据库和CSV中的格式叫long or stacked format（长格式或堆叠格式）。下面我们加载一些数据，处理一下时间序列文件并做一些数据清理工作："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>quarter</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>realcons</th>\n",
       "      <th>realinv</th>\n",
       "      <th>realgovt</th>\n",
       "      <th>realdpi</th>\n",
       "      <th>cpi</th>\n",
       "      <th>m1</th>\n",
       "      <th>tbilrate</th>\n",
       "      <th>unemp</th>\n",
       "      <th>pop</th>\n",
       "      <th>infl</th>\n",
       "      <th>realint</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>1707.4</td>\n",
       "      <td>286.898</td>\n",
       "      <td>470.045</td>\n",
       "      <td>1886.9</td>\n",
       "      <td>28.98</td>\n",
       "      <td>139.7</td>\n",
       "      <td>2.82</td>\n",
       "      <td>5.8</td>\n",
       "      <td>177.146</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>1733.7</td>\n",
       "      <td>310.859</td>\n",
       "      <td>481.301</td>\n",
       "      <td>1919.7</td>\n",
       "      <td>29.15</td>\n",
       "      <td>141.7</td>\n",
       "      <td>3.08</td>\n",
       "      <td>5.1</td>\n",
       "      <td>177.830</td>\n",
       "      <td>2.34</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>1751.8</td>\n",
       "      <td>289.226</td>\n",
       "      <td>491.260</td>\n",
       "      <td>1916.4</td>\n",
       "      <td>29.35</td>\n",
       "      <td>140.5</td>\n",
       "      <td>3.82</td>\n",
       "      <td>5.3</td>\n",
       "      <td>178.657</td>\n",
       "      <td>2.74</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>1753.7</td>\n",
       "      <td>299.356</td>\n",
       "      <td>484.052</td>\n",
       "      <td>1931.3</td>\n",
       "      <td>29.37</td>\n",
       "      <td>140.0</td>\n",
       "      <td>4.33</td>\n",
       "      <td>5.6</td>\n",
       "      <td>179.386</td>\n",
       "      <td>0.27</td>\n",
       "      <td>4.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1960.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>1770.5</td>\n",
       "      <td>331.722</td>\n",
       "      <td>462.199</td>\n",
       "      <td>1955.5</td>\n",
       "      <td>29.54</td>\n",
       "      <td>139.6</td>\n",
       "      <td>3.50</td>\n",
       "      <td>5.2</td>\n",
       "      <td>180.007</td>\n",
       "      <td>2.31</td>\n",
       "      <td>1.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     year  quarter   realgdp  realcons  realinv  realgovt  realdpi    cpi  \\\n",
       "0  1959.0      1.0  2710.349    1707.4  286.898   470.045   1886.9  28.98   \n",
       "1  1959.0      2.0  2778.801    1733.7  310.859   481.301   1919.7  29.15   \n",
       "2  1959.0      3.0  2775.488    1751.8  289.226   491.260   1916.4  29.35   \n",
       "3  1959.0      4.0  2785.204    1753.7  299.356   484.052   1931.3  29.37   \n",
       "4  1960.0      1.0  2847.699    1770.5  331.722   462.199   1955.5  29.54   \n",
       "\n",
       "      m1  tbilrate  unemp      pop  infl  realint  \n",
       "0  139.7      2.82    5.8  177.146  0.00     0.00  \n",
       "1  141.7      3.08    5.1  177.830  2.34     0.74  \n",
       "2  140.5      3.82    5.3  178.657  2.74     1.09  \n",
       "3  140.0      4.33    5.6  179.386  0.27     4.06  \n",
       "4  139.6      3.50    5.2  180.007  2.31     1.19  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('../examples/macrodata.csv')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "periods = pd.PeriodIndex(year=data.year, quarter=data.quarter,\n",
    "                         name='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "columns = pd.Index(['realgdp', 'infl', 'unemp'], name='item')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = data.reindex(columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data.index = periods.to_timestamp('D', 'end')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ldata = data.stack().reset_index().rename(columns={0: 'value'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对于PeriodIndex，我们会在第十一章讲得更详细些。在这里，这个函数把year和quarter这两列整合起来作为一种时间间隔类型。\n",
    "\n",
    "ldata看起来是这样的："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>item</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959-03-31</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2710.349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959-03-31</td>\n",
       "      <td>infl</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959-03-31</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959-06-30</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2778.801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1959-06-30</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1959-06-30</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1959-09-30</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2775.488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1959-09-30</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1959-09-30</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1959-12-31</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2785.204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date     item     value\n",
       "0 1959-03-31  realgdp  2710.349\n",
       "1 1959-03-31     infl     0.000\n",
       "2 1959-03-31    unemp     5.800\n",
       "3 1959-06-30  realgdp  2778.801\n",
       "4 1959-06-30     infl     2.340\n",
       "5 1959-06-30    unemp     5.100\n",
       "6 1959-09-30  realgdp  2775.488\n",
       "7 1959-09-30     infl     2.740\n",
       "8 1959-09-30    unemp     5.300\n",
       "9 1959-12-31  realgdp  2785.204"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldata[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这种格式叫做long format for multiple time series（用于多重时间序列的长格式），或者有两个以上键（keys）的观测数据（在这个例子里，keys指的是date和item）。表格中的每一行表示一个观测数据。\n",
    "\n",
    "这种数据经常被存储于关系型数据库中，比如MySQL，这种固定的模式（列名和数据类型）能让作为item列中不同的数据，添加到表格中。在前一个例子里，date和item通常被用来当做primary keys（主键，这是关系型数据库里的术语），能实现relational integrity（关系完整性）和更方便的join（联结）。但是在一些例子里，这种格式的数据并不好处理；我们可能更喜欢有一个DataFrame，其中一列能有不同的item值，并用date列作为索引。DataFrame中的pivot方法，就能做到这种转换："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-03-31</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-06-30</th>\n",
       "      <td>0.14</td>\n",
       "      <td>2834.390</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-09-30</th>\n",
       "      <td>2.70</td>\n",
       "      <td>2839.022</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-12-31</th>\n",
       "      <td>1.21</td>\n",
       "      <td>2802.616</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961-03-31</th>\n",
       "      <td>-0.40</td>\n",
       "      <td>2819.264</td>\n",
       "      <td>6.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961-06-30</th>\n",
       "      <td>1.47</td>\n",
       "      <td>2872.005</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961-09-30</th>\n",
       "      <td>0.80</td>\n",
       "      <td>2918.419</td>\n",
       "      <td>6.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961-12-31</th>\n",
       "      <td>0.80</td>\n",
       "      <td>2977.830</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1962-03-31</th>\n",
       "      <td>2.26</td>\n",
       "      <td>3031.241</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1962-06-30</th>\n",
       "      <td>0.13</td>\n",
       "      <td>3064.709</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1962-09-30</th>\n",
       "      <td>2.11</td>\n",
       "      <td>3093.047</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1962-12-31</th>\n",
       "      <td>0.79</td>\n",
       "      <td>3100.563</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-03-31</th>\n",
       "      <td>0.53</td>\n",
       "      <td>3141.087</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-06-30</th>\n",
       "      <td>2.75</td>\n",
       "      <td>3180.447</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-09-30</th>\n",
       "      <td>0.78</td>\n",
       "      <td>3240.332</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963-12-31</th>\n",
       "      <td>2.46</td>\n",
       "      <td>3264.967</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1964-03-31</th>\n",
       "      <td>0.13</td>\n",
       "      <td>3338.246</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1964-06-30</th>\n",
       "      <td>0.90</td>\n",
       "      <td>3376.587</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1964-09-30</th>\n",
       "      <td>1.29</td>\n",
       "      <td>3422.469</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1964-12-31</th>\n",
       "      <td>2.05</td>\n",
       "      <td>3431.957</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-03-31</th>\n",
       "      <td>1.28</td>\n",
       "      <td>3516.251</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-06-30</th>\n",
       "      <td>2.54</td>\n",
       "      <td>3563.960</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-09-30</th>\n",
       "      <td>0.89</td>\n",
       "      <td>3636.285</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1965-12-31</th>\n",
       "      <td>2.90</td>\n",
       "      <td>3724.014</td>\n",
       "      <td>4.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1966-03-31</th>\n",
       "      <td>4.99</td>\n",
       "      <td>3815.423</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1966-06-30</th>\n",
       "      <td>2.10</td>\n",
       "      <td>3828.124</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002-06-30</th>\n",
       "      <td>1.56</td>\n",
       "      <td>11538.770</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002-09-30</th>\n",
       "      <td>2.66</td>\n",
       "      <td>11596.430</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002-12-31</th>\n",
       "      <td>3.08</td>\n",
       "      <td>11598.824</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-03-31</th>\n",
       "      <td>1.31</td>\n",
       "      <td>11645.819</td>\n",
       "      <td>5.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-06-30</th>\n",
       "      <td>1.09</td>\n",
       "      <td>11738.706</td>\n",
       "      <td>6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-09-30</th>\n",
       "      <td>2.60</td>\n",
       "      <td>11935.461</td>\n",
       "      <td>6.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003-12-31</th>\n",
       "      <td>3.02</td>\n",
       "      <td>12042.817</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004-03-31</th>\n",
       "      <td>2.35</td>\n",
       "      <td>12127.623</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004-06-30</th>\n",
       "      <td>3.61</td>\n",
       "      <td>12213.818</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004-09-30</th>\n",
       "      <td>3.58</td>\n",
       "      <td>12303.533</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004-12-31</th>\n",
       "      <td>2.09</td>\n",
       "      <td>12410.282</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-03-31</th>\n",
       "      <td>4.15</td>\n",
       "      <td>12534.113</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-06-30</th>\n",
       "      <td>1.85</td>\n",
       "      <td>12587.535</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-09-30</th>\n",
       "      <td>9.14</td>\n",
       "      <td>12683.153</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-12-31</th>\n",
       "      <td>0.40</td>\n",
       "      <td>12748.699</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-03-31</th>\n",
       "      <td>2.60</td>\n",
       "      <td>12915.938</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-06-30</th>\n",
       "      <td>3.97</td>\n",
       "      <td>12962.462</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-09-30</th>\n",
       "      <td>-1.58</td>\n",
       "      <td>12965.916</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-12-31</th>\n",
       "      <td>3.30</td>\n",
       "      <td>13060.679</td>\n",
       "      <td>4.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-03-31</th>\n",
       "      <td>4.58</td>\n",
       "      <td>13099.901</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-06-30</th>\n",
       "      <td>2.75</td>\n",
       "      <td>13203.977</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-09-30</th>\n",
       "      <td>3.45</td>\n",
       "      <td>13321.109</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-12-31</th>\n",
       "      <td>6.38</td>\n",
       "      <td>13391.249</td>\n",
       "      <td>4.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-03-31</th>\n",
       "      <td>2.82</td>\n",
       "      <td>13366.865</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-06-30</th>\n",
       "      <td>8.53</td>\n",
       "      <td>13415.266</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-09-30</th>\n",
       "      <td>-3.16</td>\n",
       "      <td>13324.600</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-12-31</th>\n",
       "      <td>-8.79</td>\n",
       "      <td>13141.920</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-03-31</th>\n",
       "      <td>0.94</td>\n",
       "      <td>12925.410</td>\n",
       "      <td>8.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-30</th>\n",
       "      <td>3.37</td>\n",
       "      <td>12901.504</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-09-30</th>\n",
       "      <td>3.56</td>\n",
       "      <td>12990.341</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>203 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "item        infl    realgdp  unemp\n",
       "date                              \n",
       "1959-03-31  0.00   2710.349    5.8\n",
       "1959-06-30  2.34   2778.801    5.1\n",
       "1959-09-30  2.74   2775.488    5.3\n",
       "1959-12-31  0.27   2785.204    5.6\n",
       "1960-03-31  2.31   2847.699    5.2\n",
       "1960-06-30  0.14   2834.390    5.2\n",
       "1960-09-30  2.70   2839.022    5.6\n",
       "1960-12-31  1.21   2802.616    6.3\n",
       "1961-03-31 -0.40   2819.264    6.8\n",
       "1961-06-30  1.47   2872.005    7.0\n",
       "1961-09-30  0.80   2918.419    6.8\n",
       "1961-12-31  0.80   2977.830    6.2\n",
       "1962-03-31  2.26   3031.241    5.6\n",
       "1962-06-30  0.13   3064.709    5.5\n",
       "1962-09-30  2.11   3093.047    5.6\n",
       "1962-12-31  0.79   3100.563    5.5\n",
       "1963-03-31  0.53   3141.087    5.8\n",
       "1963-06-30  2.75   3180.447    5.7\n",
       "1963-09-30  0.78   3240.332    5.5\n",
       "1963-12-31  2.46   3264.967    5.6\n",
       "1964-03-31  0.13   3338.246    5.5\n",
       "1964-06-30  0.90   3376.587    5.2\n",
       "1964-09-30  1.29   3422.469    5.0\n",
       "1964-12-31  2.05   3431.957    5.0\n",
       "1965-03-31  1.28   3516.251    4.9\n",
       "1965-06-30  2.54   3563.960    4.7\n",
       "1965-09-30  0.89   3636.285    4.4\n",
       "1965-12-31  2.90   3724.014    4.1\n",
       "1966-03-31  4.99   3815.423    3.9\n",
       "1966-06-30  2.10   3828.124    3.8\n",
       "...          ...        ...    ...\n",
       "2002-06-30  1.56  11538.770    5.8\n",
       "2002-09-30  2.66  11596.430    5.7\n",
       "2002-12-31  3.08  11598.824    5.8\n",
       "2003-03-31  1.31  11645.819    5.9\n",
       "2003-06-30  1.09  11738.706    6.2\n",
       "2003-09-30  2.60  11935.461    6.1\n",
       "2003-12-31  3.02  12042.817    5.8\n",
       "2004-03-31  2.35  12127.623    5.7\n",
       "2004-06-30  3.61  12213.818    5.6\n",
       "2004-09-30  3.58  12303.533    5.4\n",
       "2004-12-31  2.09  12410.282    5.4\n",
       "2005-03-31  4.15  12534.113    5.3\n",
       "2005-06-30  1.85  12587.535    5.1\n",
       "2005-09-30  9.14  12683.153    5.0\n",
       "2005-12-31  0.40  12748.699    4.9\n",
       "2006-03-31  2.60  12915.938    4.7\n",
       "2006-06-30  3.97  12962.462    4.7\n",
       "2006-09-30 -1.58  12965.916    4.7\n",
       "2006-12-31  3.30  13060.679    4.4\n",
       "2007-03-31  4.58  13099.901    4.5\n",
       "2007-06-30  2.75  13203.977    4.5\n",
       "2007-09-30  3.45  13321.109    4.7\n",
       "2007-12-31  6.38  13391.249    4.8\n",
       "2008-03-31  2.82  13366.865    4.9\n",
       "2008-06-30  8.53  13415.266    5.4\n",
       "2008-09-30 -3.16  13324.600    6.0\n",
       "2008-12-31 -8.79  13141.920    6.9\n",
       "2009-03-31  0.94  12925.410    8.1\n",
       "2009-06-30  3.37  12901.504    9.2\n",
       "2009-09-30  3.56  12990.341    9.6\n",
       "\n",
       "[203 rows x 3 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted = ldata.pivot('date', 'item', 'value')\n",
    "pivoted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "前两个传入的值是列，分别被用于作为行索引和列索引（date是行索引，item是列索引），最后是一个是可选的value column（值列），用于填充DataFrame。假设我们有两列值，我们想要同时整形："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>item</th>\n",
       "      <th>value</th>\n",
       "      <th>value2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959-03-31</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>0.284176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959-03-31</td>\n",
       "      <td>infl</td>\n",
       "      <td>0.000</td>\n",
       "      <td>-1.428019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959-03-31</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.800</td>\n",
       "      <td>-1.238330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959-06-30</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>0.144937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1959-06-30</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.340</td>\n",
       "      <td>-0.553085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1959-06-30</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.100</td>\n",
       "      <td>-0.833374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1959-09-30</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>0.147826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1959-09-30</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.740</td>\n",
       "      <td>-0.059040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1959-09-30</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.300</td>\n",
       "      <td>0.531887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1959-12-31</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>-0.169839</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date     item     value    value2\n",
       "0 1959-03-31  realgdp  2710.349  0.284176\n",
       "1 1959-03-31     infl     0.000 -1.428019\n",
       "2 1959-03-31    unemp     5.800 -1.238330\n",
       "3 1959-06-30  realgdp  2778.801  0.144937\n",
       "4 1959-06-30     infl     2.340 -0.553085\n",
       "5 1959-06-30    unemp     5.100 -0.833374\n",
       "6 1959-09-30  realgdp  2775.488  0.147826\n",
       "7 1959-09-30     infl     2.740 -0.059040\n",
       "8 1959-09-30    unemp     5.300  0.531887\n",
       "9 1959-12-31  realgdp  2785.204 -0.169839"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldata['value2'] = np.random.randn(len(ldata))\n",
    "ldata[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "舍弃最后一个参数，我们能得到一个有多层级列的DataFrame："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">value</th>\n",
       "      <th colspan=\"3\" halign=\"left\">value2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-03-31</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-1.428019</td>\n",
       "      <td>0.284176</td>\n",
       "      <td>-1.238330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "      <td>-0.553085</td>\n",
       "      <td>0.144937</td>\n",
       "      <td>-0.833374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "      <td>-0.059040</td>\n",
       "      <td>0.147826</td>\n",
       "      <td>0.531887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-0.436330</td>\n",
       "      <td>-0.169839</td>\n",
       "      <td>-0.203380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.038559</td>\n",
       "      <td>0.644242</td>\n",
       "      <td>-1.872500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           value                    value2                    \n",
       "item        infl   realgdp unemp      infl   realgdp     unemp\n",
       "date                                                          \n",
       "1959-03-31  0.00  2710.349   5.8 -1.428019  0.284176 -1.238330\n",
       "1959-06-30  2.34  2778.801   5.1 -0.553085  0.144937 -0.833374\n",
       "1959-09-30  2.74  2775.488   5.3 -0.059040  0.147826  0.531887\n",
       "1959-12-31  0.27  2785.204   5.6 -0.436330 -0.169839 -0.203380\n",
       "1960-03-31  2.31  2847.699   5.2  1.038559  0.644242 -1.872500"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted = ldata.pivot('date', 'item')\n",
    "pivoted[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-03-31</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "item        infl   realgdp  unemp\n",
       "date                             \n",
       "1959-03-31  0.00  2710.349    5.8\n",
       "1959-06-30  2.34  2778.801    5.1\n",
       "1959-09-30  2.74  2775.488    5.3\n",
       "1959-12-31  0.27  2785.204    5.6\n",
       "1960-03-31  2.31  2847.699    5.2"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted['value'][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这里pivot相当于用set_index创建了一个多层级用里，并调用了unstack："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">value</th>\n",
       "      <th colspan=\"3\" halign=\"left\">value2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-03-31</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-1.428019</td>\n",
       "      <td>0.284176</td>\n",
       "      <td>-1.238330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "      <td>-0.553085</td>\n",
       "      <td>0.144937</td>\n",
       "      <td>-0.833374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "      <td>-0.059040</td>\n",
       "      <td>0.147826</td>\n",
       "      <td>0.531887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-0.436330</td>\n",
       "      <td>-0.169839</td>\n",
       "      <td>-0.203380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.038559</td>\n",
       "      <td>0.644242</td>\n",
       "      <td>-1.872500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-06-30</th>\n",
       "      <td>0.14</td>\n",
       "      <td>2834.390</td>\n",
       "      <td>5.2</td>\n",
       "      <td>0.765028</td>\n",
       "      <td>-0.835805</td>\n",
       "      <td>2.014334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-09-30</th>\n",
       "      <td>2.70</td>\n",
       "      <td>2839.022</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.168310</td>\n",
       "      <td>0.362230</td>\n",
       "      <td>1.603777</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           value                    value2                    \n",
       "item        infl   realgdp unemp      infl   realgdp     unemp\n",
       "date                                                          \n",
       "1959-03-31  0.00  2710.349   5.8 -1.428019  0.284176 -1.238330\n",
       "1959-06-30  2.34  2778.801   5.1 -0.553085  0.144937 -0.833374\n",
       "1959-09-30  2.74  2775.488   5.3 -0.059040  0.147826  0.531887\n",
       "1959-12-31  0.27  2785.204   5.6 -0.436330 -0.169839 -0.203380\n",
       "1960-03-31  2.31  2847.699   5.2  1.038559  0.644242 -1.872500\n",
       "1960-06-30  0.14  2834.390   5.2  0.765028 -0.835805  2.014334\n",
       "1960-09-30  2.70  2839.022   5.6  0.168310  0.362230  1.603777"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unstacked = ldata.set_index(['date', 'item']).unstack('item')\n",
    "unstacked[:7]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3 Pivoting “Wide” to “Long” Format（把“宽”格式旋转为“长”格式）\n",
    "\n",
    "用于DataFrame，与pivot相反的操作是pandas.melt。相对于把一列变为多列的pivot，melt会把多列变为一列，产生一个比输入的DataFrame还要长的结果。看一下例子："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>bar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>baz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C  key\n",
       "0  1  4  7  foo\n",
       "1  2  5  8  bar\n",
       "2  3  6  9  baz"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'key': ['foo', 'bar', 'baz'], \n",
    "                   'A': [1, 2, 3], \n",
    "                   'B': [4, 5, 6], \n",
    "                   'C': [7, 8, 9]})\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'key'列可以作为group indicator（群指示器），其他列可以作为数据值。当使用pandas.melt，我们必须指明哪些列是群指示器。这里我们令key作为群指示器："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>baz</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>baz</td>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>foo</td>\n",
       "      <td>C</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>bar</td>\n",
       "      <td>C</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>baz</td>\n",
       "      <td>C</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   key variable  value\n",
       "0  foo        A      1\n",
       "1  bar        A      2\n",
       "2  baz        A      3\n",
       "3  foo        B      4\n",
       "4  bar        B      5\n",
       "5  baz        B      6\n",
       "6  foo        C      7\n",
       "7  bar        C      8\n",
       "8  baz        C      9"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melted = pd.melt(df, ['key'])\n",
    "melted"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用pivot，我们可以得到原来的布局："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>variable</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>baz</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variable  A  B  C\n",
       "key              \n",
       "bar       2  5  8\n",
       "baz       3  6  9\n",
       "foo       1  4  7"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reshaped = melted.pivot('key', 'variable', 'value')\n",
    "reshaped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "因为pivot会给行标签创建一个索引（key列），所以这里我们要用reset_index来让数据变回去："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>variable</th>\n",
       "      <th>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bar</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>baz</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foo</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variable  key  A  B  C\n",
       "0         bar  2  5  8\n",
       "1         baz  3  6  9\n",
       "2         foo  1  4  7"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reshaped.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当然，我们也可以在使用melt的时候指定哪些列用于值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>baz</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>baz</td>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   key variable  value\n",
       "0  foo        A      1\n",
       "1  bar        A      2\n",
       "2  baz        A      3\n",
       "3  foo        B      4\n",
       "4  bar        B      5\n",
       "5  baz        B      6"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(df, id_vars=['key'], value_vars=['A', 'B'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas.melt也能在没有群指示器的情况下使用："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  variable  value\n",
       "0        A      1\n",
       "1        A      2\n",
       "2        A      3\n",
       "3        B      4\n",
       "4        B      5\n",
       "5        B      6\n",
       "6        C      7\n",
       "7        C      8\n",
       "8        C      9"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(df, value_vars=['A', 'B', 'C'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>key</td>\n",
       "      <td>foo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>key</td>\n",
       "      <td>bar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>key</td>\n",
       "      <td>baz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  variable value\n",
       "0      key   foo\n",
       "1      key   bar\n",
       "2      key   baz\n",
       "3        A     1\n",
       "4        A     2\n",
       "5        A     3\n",
       "6        B     4\n",
       "7        B     5\n",
       "8        B     6"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(df, value_vars=['key','A', 'B'])"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [py35]",
   "language": "python",
   "name": "Python [py35]"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
